using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

namespace lu596
{
    public partial class FrmBaocaoTongdai : Form
    {
        //instance fields
        private export2Excel export2XLS;
        private DataSet _dataSet;

        public FrmBaocaoTongdai()
        {
            InitializeComponent();
        }

        //load the gridview using the local access database
        private void Form1_Load(object sender, EventArgs e)
        {

            Directory.SetCurrentDirectory(Application.StartupPath + @"..\..\..\");
            String accessPath = Directory.GetCurrentDirectory() + @"\Northwind.mdb";

            // Set the connection and sql strings
            // assumes your mdb file is in your root
            //string connString = @"Provider=Microsoft.JET.OLEDB.4.0;data source=" + accessPath;
            string connString = @"Data Source=QUOCSY\SQLEXPRESS;Initial Catalog=ThietBi;User ID=sa;pwd=sql2005";
            //string sqlString = "Select * from virtualTable1";
            string sqlString = "SELECT STT,Ten_tong_dai,Loai_tong_dai," +
" Dl_max,Dl_sd,Dl_a,Dl_b,Dl_c,Dl_d,Tb_max,Tb_sd,Tb_a,Tb_b,Tb_c,Tb_d,Quy_so,Don_vi_ql,Dia_danh,Huong_kn" +
" from dbo.virtualTable1";
            SqlDataAdapter dataAdapter = null;
            //OleDbDataAdapter dataAdapter = null;
            _dataSet = null;

            try
            {
                // Connection object
                SqlConnection connection = new SqlConnection(connString);
                //OleDbConnection connection = new OleDbConnection(connString);

                // Create data adapter object
                dataAdapter = new SqlDataAdapter(sqlString, connection);
                //dataAdapter = new OleDbDataAdapter(sqlString, connection);

                // Create a dataset object and fill with data using data adapter's Fill method
                _dataSet = new DataSet();
                dataAdapter.Fill(_dataSet, "virtualTable1");
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem with DB access-\n\n   connection: "
                    + connString + "\r\n\r\n            query: " + sqlString
                    + "\r\n\r\n\r\n" + ex.ToString());
                this.Close();
                return;
            }

            DataView dvCust = _dataSet.Tables["virtualTable1"].DefaultView;
            dg1.DataSource = dvCust;
           
        }

        //Do a straight export to a new Excel document of the gridviews grid data
        private void btn2Excel_Click(object sender, EventArgs e)
        {

            //show a file save dialog and ensure the user selects
            //correct file to allow the export
            saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if (!saveFileDialog1.FileName.Equals(String.Empty))
                {
                    FileInfo f = new FileInfo(saveFileDialog1.FileName);
                    if (f.Extension.Equals(".xls"))
                    {
                        StartExport(saveFileDialog1.FileName);
                    }
                    else
                    {
                        MessageBox.Show("Invalid file type");
                    }
                }
                else
                {
                    MessageBox.Show("You did pick a location to save file to");
                }
            }
        }

        //starts the export to new excel document
        //@param filepath : the file to export to
        private void StartExport(String filepath)
        {
            btn2Excel.Enabled = false;
            btnUseTemplate.Enabled = false;
            //create a new background worker, to do the exporting
            BackgroundWorker bg = new BackgroundWorker();
            bg.DoWork += new DoWorkEventHandler(bg_DoWork);
            bg.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
            bg.RunWorkerAsync(filepath);

            //create a new export2XLS object, providing DataView as a input parameter
            export2XLS = new export2Excel();
            export2XLS.prg += new export2Excel.ProgressHandler(export2XLS_prg);
        }

        //do the new excel document work using the background worker
        private void bg_DoWork(object sender, DoWorkEventArgs e)
        {
            //get the Gridviews DataView
            DataView dv = _dataSet.Tables["virtualTable1"].DefaultView;
            //Pass the path and the sheet to use
            export2XLS.ExportToExcel(dv, (String)e.Argument, "newSheet1");
        }

        //Do a export to a new Excel document, with the use of a custom template
        private void btnUseTemplate_Click(object sender, EventArgs e)
        {
            //show a file save dialog and ensure the user selects
            //correct file to allow the export
            saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if (!saveFileDialog1.FileName.Equals(String.Empty))
                {
                    FileInfo f = new FileInfo(saveFileDialog1.FileName);
                    if (f.Extension.Equals(".xls"))
                    {
                        StartExportUseTemplate(saveFileDialog1.FileName);
                    }
                    else
                    {
                        MessageBox.Show("Invalid file type");
                    }
                }
                else
                {
                    MessageBox.Show("You did pick a location to save file to");
                }
            }
        }

        //Create a new excel document from a given template
        //@param filepath : the file to export to
        private void StartExportUseTemplate(String filepath)
        {
            btn2Excel.Enabled = false;
            btnUseTemplate.Enabled = false;
            //create a new background worker, to do the exporting
            BackgroundWorker bg = new BackgroundWorker();
            bg.DoWork += new DoWorkEventHandler(bg_DoWorkUseTemplate);
            bg.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
            bg.RunWorkerAsync(filepath);

            //create a new export2XLS object, providing DataView as a input parameter
            export2XLS = new export2Excel();
            export2XLS.prg += new export2Excel.ProgressHandler(export2XLS_prg);
        }

        //Do the adding to custom template to create new excel document, work using the background worker
        private void bg_DoWorkUseTemplate(object sender, DoWorkEventArgs e)
        {
            //create some data to whack in the template
            String[,] templateValues =  {    { "task1", "CompletedBy1", "CompletedDate1" }, 
                                             { "task2", "CompletedBy2", "CompletedDate2" } 
                                        };

            //The template path to use
            Directory.SetCurrentDirectory(Application.StartupPath + @"..\..\..\");
            String templatePath = Directory.GetCurrentDirectory() + @"\TaskList.xlt";

            //Pass the template path and the test values and fill the template
            export2XLS.UseTemplate((String)e.Argument, templatePath, templateValues);
        }

        //Update the progress bar with the a value
        private void export2XLS_prg(object sender, ProgressEventArgs e)
        {
            //need to call InvokeRequired to check if thread need marshalling, to get the thread onto 
            //the same thread as the thread who owns the controls
            if (this.InvokeRequired)
            {
                this.Invoke(new EventHandler(delegate
                {
                    progressBar1.Value = e.ProgressValue;
                }));
            }
            else
            {
                progressBar1.Value = e.ProgressValue;
            }
        }
    
        //show a message to the user when the background worker has finished
        //and re-enable the export buttons
        private void bg_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            btn2Excel.Enabled = true;
            btnUseTemplate.Enabled = true;
            MessageBox.Show("Finished");
        }
    }
}